﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Runda.EShop.Common.Utility;
using Runda.EShop.Common.Entity;
using System.Text;

namespace Runda.EShop.Dal
{
    /// <summary>
    /// DAL layer class dbo.Biz_Customer_Favorite.
    /// </summary>
    public partial class BizCustomerFavoriteDal
    {
        public void AddCustomerFavorite(BizCustomerFavoriteEntity entity)
        {
            string sql = @"
IF NOT EXISTS (SELECT 1 FROM Biz_Customer_Favorite WHERE CustomerID= @CustomerID AND ProductID=@ProductID)
    INSERT INTO Biz_Customer_Favorite (ID,CustomerID,ProductID,CreatedAt,Deleted) values (@ID,@CustomerID,@ProductID,@CreatedAt,@Deleted)";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            CurrentDatabase.AddInParameter(dbcommand, "@ID", DbType.Guid, entity.ID);
            CurrentDatabase.AddInParameter(dbcommand, "@CustomerID", DbType.Guid, entity.CustomerID);
            CurrentDatabase.AddInParameter(dbcommand, "@ProductID", DbType.Guid, entity.ProductID);
            CurrentDatabase.AddInParameter(dbcommand, "@CreatedAt", DbType.DateTime, entity.CreatedAt);
            CurrentDatabase.AddInParameter(dbcommand, "@Deleted", DbType.Boolean, entity.Deleted);

            CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public DataTable GetCustomerFavorite(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT * FROM (SELECT {0} A.*,B.ProductName,B.ProductCode,B.Alias,B.Standard,b.Description FROM Biz_Customer_Favorite A JOIN Bas_Product B ON A.ProductID=B.ProductID) AS TB WHERE Deleted = 0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }
}
